package com.soda.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.soda.bean.Information;
import com.soda.bean.News;
import com.soda.bean.Notice;
import com.soda.dao.BaseDao;
import com.soda.dao.InformationDao;
import com.soda.util.PageInfo;
import com.soda.util.SelectInformInfo;
import com.soda.util.SelectNoticeInfo;

public class InformationDaoImpl extends BaseDao implements InformationDao {
	public int addInform(Information inf) {
		try {
			getConnection();
			String sql = "INSERT INTO information VALUES(default,?,?,?,NOW(),1)";
			pt = conn.prepareStatement(sql);
			pt.setString(1, inf.getInfTitle());
			pt.setInt(2, inf.getInfState());
			pt.setString(3,inf.getInfContent());
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	// 全查
	public Information showAllInform(int id) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql = "select * from information where inf_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			res = pt.executeQuery();
			if (res.next()) {
				Information n = new Information(res.getInt(1), res.getString(2),
						 res.getInt(3),res.getString(4),
						 res.getString(5), res.getInt(6));

				return n;

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}

	// 条件查询
 
	public List<Information> getInform(SelectInformInfo sii, PageInfo pi) {
		
	 {
		// , PageInfo pi
		
			try {
				getConnection();
				StringBuffer sql = new StringBuffer(
						"select * from information where inf_delete=1");
				if (sii != null) {
					if (sii.getInfTitle()!= null
							|| sii.getInfTitle().length() != 0) {
						sql.append(" and inf_title like ?");
					}
					if (sii.getInfUpdateTime() != null
							|| sii.getInfUpdateTime().length() != 0) {
						sql.append(" and inf_updatetime like ?");
					}

					if (sii.getInfState() !=0) {
						sql.append(" and inf_state=?");
					}

				}
				sql.append(" order by inf_id desc limit ?,? ");
				pt = conn.prepareStatement(sql.toString());
				int x = 1;
				if (sii != null) {
					if (sii.getInfTitle() != null
							|| sii.getInfTitle().length() != 0) {
						pt.setString(x, sii.getInfTitle() + "%");
						x++;
					}
					if (sii.getInfUpdateTime() != null
							|| sii.getInfUpdateTime().length() != 0) {
						pt.setString(x, sii.getInfUpdateTime() + "%");
						x++;
					}
					if (sii.getInfState() >= 0) {
						if (sii.getInfState() !=0) {
							pt.setInt(x, sii.getInfState());
							x++;
						}
					}
				}
				// 分页信息
				pt.setInt(x, (pi.getPage() - 1) * pi.getPer());
				x++;
				pt.setInt(x, pi.getPer());
				List<Information> list = new ArrayList<Information>();
				res = pt.executeQuery();
				while (res.next()) {
					Information inform = new Information();
					inform.setInfId(res.getInt(1));
					inform.setInfTitle(res.getString(2));
					inform.setInfState(res.getInt(3));
					inform.setInfContent(res.getString(4));
					inform.setInfUpdateTime(res.getString(5));
					inform.setInfDelete(res.getInt(6));
					list.add(inform);
				}
				return list;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				close();
			}return null;
	}
	
	}

	public int getInformCount(SelectInformInfo sii) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			StringBuffer sql = new StringBuffer(
					"select count(1) from information where inf_delete=1");
			if (sii != null) {
				if (sii.getInfTitle() != null
						|| sii.getInfTitle().length() != 0) {
					sql.append(" and inf_title like ?");
				}
				if (sii.getInfUpdateTime() != null
						|| sii.getInfUpdateTime().length() != 0) {
					sql.append(" and inf_updatetime like ?");
				}

				if (sii.getInfState()!=0) {
					sql.append(" and inf_state=?");
				}

			}
			pt = conn.prepareStatement(sql.toString());
			int x = 1;
			if (sii != null) {
				if (sii.getInfTitle()!= null || sii.getInfTitle().length() != 0) {
					pt.setString(x, sii.getInfTitle() + "%");
					x++;
				}
				if (sii.getInfUpdateTime() != null
						|| sii.getInfUpdateTime().length() != 0) {
					pt.setString(x, sii.getInfUpdateTime() + "%");
					x++;
				}
				if (sii.getInfState()>= 0) {
					if (sii.getInfState() != 0 ) {
						pt.setInt(x, sii.getInfState());
						x++;
					}
				}
			}
			res = pt.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return 0;
	}

	public List<Information> selectInform(PageInfo pi) {
		try {
			getConnection();
			String sql = "select * from information limit ?,?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, (pi.getPage() - 1) * pi.getPer());
			pt.setInt(2, pi.getPer());
			res = pt.executeQuery();
			List<Information> list = new ArrayList<Information>();
			while (res.next()) {
				Information inform = new Information(res.getInt(1), res.getString(2),
						 res.getInt(3),res.getString(4), res.getString(5),
				   res.getInt(7));
				list.add(inform);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	// 单删
	public int deleteById(int id) {

		try {
			getConnection();
			String sql = "UPDATE information SET inf_delete=0 WHERE inf_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, id);
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();

		}
		return 0;
	}

	// 批量删除
	public int delAllInform(String[] list) {
	
			try {
				getConnection();
				StringBuffer sql = new StringBuffer("update information set inf_delete=0 where inf_id in( ");
				 for(int i=0;i<list.length;i++){
					 sql.append("?,");
				 }
				sql.deleteCharAt(sql.length() - 1);
				sql.append(")");
				System.out.println(sql);
				pt = conn.prepareStatement(sql.toString());
				int x=1;
				for(int i=0;i<list.length;i++){
					pt.setInt(x,Integer.parseInt(list[i]));
					x++;
				}
				return pt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				close();
			}

			return 0;
		}
	
	//导出
	public List<Notice>expolExcel(String[] nids) {
		try {
			getConnection();
			StringBuffer sql = new StringBuffer(
					"select not_id,not_title,not_updatetime,not_level from notice where not_id in(");
			for (String i : nids) {
				sql.append(i + ",");
			}
			sql.deleteCharAt(sql.length() - 1);
			sql.append(")");

			pt = conn.prepareStatement(sql.toString());
			List<Notice>list=new ArrayList<Notice>();
			res=pt.executeQuery();
			while(res.next()){
				Notice n=new Notice();
				n.setNotId(res.getInt(1));
				n.setNotTitle(res.getString(2));
				n.setNotUpdateTime(res.getString(3));
				n.setNotLevel(res.getInt(4));
				list.add(n);
			}
		return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return null;
	}
	
	
	// 修改

	
	public int updateInform(int id, int recommend, String neirong) {

		try {
			getConnection();
			String sql = "UPDATE information SET inf_state=?,inf_content=?,inf_updatetime=NOW()  WHERE inf_id=?";
			pt = conn.prepareStatement(sql);
			pt.setInt(1, recommend);
			pt.setString(2, neirong);
			pt.setInt(3, id);
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}

		return 0;
	}
	public Information getinfTitleSelect(String inftitle) {
		try {
			// TODO Auto-generated method stub
			getConnection();
			String sql = "Select * from information where inf_title=?";
			pt = conn.prepareStatement(sql);
			pt.setString(1, inftitle);
			res=pt.executeQuery();
			List<Information> list = new ArrayList<Information>();
			if(res.next()){
				Information im= new Information(res.getInt(1), res.getString(2), res.getInt(3), res.getString(4), res.getString(5), res.getInt(6));
				list.add(im);
				return im;
			}
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			close();
		}
		return null;
	}
	
	public List<Information> getSelectAll() {
		try {
			getConnection();
			String sql = "SELECT * FROM information";
			pt = conn.prepareStatement(sql);
			res = pt.executeQuery();
			List<Information> list = new ArrayList<Information>();
			while(res.next()){
				
				Information im= new Information(res.getInt(1), res.getString(2), res.getInt(3), res.getString(4), res.getString(5), res.getInt(6));
			    
				list.add(im);
			}
			return list;
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			close();
			
		}
		return null;
	}
	//修改状态
	public int updateStateNews(int newId, int state) {
		// TODO Auto-generated method stub
		try {
			getConnection();
			String sql="update information set inf_state=? where inf_id=?";
			pt=conn.prepareStatement(sql);
			pt.setInt(1, state);
			pt.setInt(2,newId);
			return pt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 0;
	}

	
	
	//导出
	public List<Information> exportInform(String[] count) {
		try{
		getConnection();
		StringBuffer sql=new StringBuffer("select * from information where inf_id in(");
		for (int i = 0; i < count.length; i++) {
			sql.append("?,");
		}
		sql.deleteCharAt(sql.length()-1);
		sql.append(")");
		pt=conn.prepareStatement(sql.toString());
		int x=1;
		for (int i = 0; i < count.length; i++) {
			pt.setInt(x, Integer.parseInt(count[i]));
			x++;
		}
		res=pt.executeQuery();
		List<Information> list=new ArrayList<Information>();
		while(res.next()){
			Information inf=new Information(res.getInt(1), res.getString(2), res.getInt(3), res.getString(4), res.getString(5), res.getInt(6));
			list.add(inf);
			
		}
		return list;
	} catch (NumberFormatException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		close();
	}	
		return null;

	}
	

}
